package com.tool.imp;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.RowSet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.kingdee.util.StringUtils;
import com.kingdee.util.db.SQLUtils;
import com.tool.BillType;
import com.tool.DBConnector;
import com.tool.DataCenterVO;
import com.tool.util.CachMap;

/**
 * 导入基类，excel2VO,process(VOValidate & saveVO)
 * 
 * @author Evan.Hu
 * 
 */
public abstract class ImportBase {
	protected static final String PstngDate_Format = "dd.MM.yyyy";
	protected static final String YYYY_MM_DD = "yyyy-MM-dd";
	protected static final String YYYY_MM_DD_HHMMSS = "yyyy-MM-dd HH:mm:ss";
	protected BillType billType;
	protected DataCenterVO dbVo;
	protected String xlsPath;
	protected String sheetName;
	protected Boolean fileFlag;
	protected Workbook wbook;
	protected org.apache.poi.ss.usermodel.Sheet sheet;
	protected XSSFWorkbook workBook2007;
	protected XSSFSheet sheet2007;

	protected int rowInx = 0;
	protected int colInx = 0;
	public CachMap cachMap;

	/** 记录业务处理过程中所有的消息 */
	protected MessageBean msgBean = new MessageBean();

	protected List<BillVO> records = new ArrayList<BillVO>();

	/**
	 * @param workBook
	 *            工作簿
	 * @param sheetName
	 *            页签名称
	 * @param billName
	 *            待导入单据名称
	 * @param bizType
	 *            待导入单据事务类型编码（或者业务类别）
	 */
	protected ImportBase(BillType billType, DataCenterVO dbVo, String xlsPath, Boolean fileFlag) {
		this.billType = billType;
		this.dbVo = dbVo;
		this.xlsPath = xlsPath;
		// 默认数据页签名
		this.sheetName = "sheet1";
		this.fileFlag = fileFlag;
	}

	static {

	}

	/**
	 * 业务逻辑处理
	 * 
	 */
	protected abstract void process() throws Exception;

	/**
	 * 解析一行记录 并检查基础资料完整性
	 * 
	 * @return
	 */
	protected abstract BillVO parseRow(Row row) throws ParseExcelException;

	/**
	 * 新增记录，或者归集合并记录
	 * 
	 * @param vo
	 */
	protected void addOrUpdateRecords(BillVO vo) {
		if (vo == null)
			return;

		BillVO existVo = null;
		String bizNumField = vo.getBizNumberField();
		// TODO 加强校验
		if (bizNumField != null) {
			// 如果设置了分组字段，则先查看已解析对象中，是否存在，如果有，则返回VO，否则返回新创建对象
			for (BillVO vo2 : records) {
				if (vo2.get(bizNumField) != null && vo.get(bizNumField) != null && vo2.get(bizNumField).equals(vo.get(bizNumField))) {
					existVo = vo2;
					break;
				}
			}
		}

		if (existVo == null) {
			records.add(vo);
		} else {
			// 单据头内容，以第一条为准，分录信息需要合并
			// 将分录增加到原有单据分录中
			compositEntry(existVo, vo);
		}
	}

	/**
	 * 检查数据库已有记录和新增保存记录，是否存在重复
	 * 
	 * @param vo
	 * @return
	 */
	protected boolean checkNumberDuplicate(BillVO vo) {
		if (vo.getBizNumberField() != null) {
			// 分组字段不为空时，检查数据库已有记录和新增保存记录，是否存在重复
			final String sqlName = vo.getFieldMap().get(vo.getBizNumberField()).getSqlName();
			Connection conn = null;

			try {
				conn = DBConnector.getJDBCConnection(dbVo);
				RowSet rs = SQLUtils.getJdbcRowSet(conn, "select 1 from " + vo.getTableName() + " where " + sqlName + "='"
						+ vo.getString(vo.getBizNumberField()) + "'");
				while (rs.next()) {
					return true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	/**
	 * 将两个VO的分录进行合并保存在destVo对象中
	 * 
	 * @param destVo
	 * @param srcVo
	 */
	protected abstract void compositEntry(BillVO destVo, BillVO srcVo);

	/**
	 * 校验数据 ，解析数字至VO对象集合中
	 */
	public void excel2VO() {
		cachMap = new CachMap(dbVo);
		long start = System.currentTimeMillis();
		try {
			msgBean.setCheckPass(checkParsed());
		} catch (Exception e) {
			e.printStackTrace();
			// checkParsed未处理过的错误类型
			msgBean.setCheckPass(false);
			msgBean.setParseErr(parseEL(MessageBean.ERR_MSG_Excel2VO, new String[] { sheetName, rowInx + "", "未知", e.getMessage() }));
		}
		msgBean.setParseCost(System.currentTimeMillis() - start);
	}

	/**
	 * 设置和保存VO进数据库
	 */
	public void setAndSaveVO() {
		long start = System.currentTimeMillis();
		try {
			if (msgBean.isCheckPass()) {
				process();
			}
		} catch (Exception e) {
			e.printStackTrace();
			msgBean.setProcessErr(parseEL(MessageBean.ERR_MSG_SetAndSave, new String[] { sheetName, e.getMessage() }));
		}
		msgBean.setSaveBillCost(System.currentTimeMillis() - start);
	}

	/**
	 * 获取日志
	 * 
	 * @return
	 */
	public String getMessage() {
		StringBuilder msg = new StringBuilder();
		msg.append(parseEL(MessageBean.SUC_MSG_Finish, new String[] { billType.toString(), Integer.valueOf(msgBean.getSucList().size()).toString(),
				Integer.valueOf(msgBean.getErrList().size()).toString(), Long.valueOf(msgBean.getParseCost()).toString(),
				Long.valueOf(msgBean.getSaveBillCost()).toString() }));

		if (msgBean.getParseErr() != null) {
			msg.append("\r\n====================解析-错误日志=========================");
			msg.append("\r\n").append(msgBean.getParseErr());
		}
		if (!msgBean.getParseErrList().isEmpty()) {
			msg.append("\r\n====================数据准备时出现错误=========================");
			for (String err : msgBean.getParseErrList()) {
				msg.append("\r\n").append(err);
			}
		}
		if (msgBean.getProcessErr() != null) {
			msg.append("\r\n====================处理日志=========================");
			msg.append("\r\n").append(msgBean.getProcessErr());
		}

		if (!msgBean.getErrList().isEmpty()) {
			msg.append("\r\n====================下面为错误日志=========================");
			for (String err : msgBean.getErrList()) {
				msg.append("\r\n").append(err);
			}
		}
		if (!msgBean.getSucList().isEmpty()) {
			msg.append("\r\n====================下面为成功日志=========================");
			for (String str : msgBean.getSucList()) {
				msg.append("\r\n").append(str);
			}
		}

		return msg.toString();
	}

	/**
	 * 常规校验
	 * 
	 * @return
	 */
	protected void commCheck() {
		try {
			// Excel为2003版本
			if (fileFlag) {

				wbook = new HSSFWorkbook(new FileInputStream(xlsPath));

				if (wbook == null) {
					msgBean.setIgnoreErr(true);
					throw new RuntimeException("工作簿为空");
				}
				sheet = wbook.getSheet(sheetName);
				if (sheet == null) {
					msgBean.setIgnoreErr(true);
					throw new RuntimeException("工作簿中不存在页签:" + sheetName);
				}

			} else {

				System.out.println("xlspath:::" + xlsPath);
				wbook = new XSSFWorkbook(new FileInputStream(xlsPath));
				if (wbook == null) {
					msgBean.setIgnoreErr(true);
					throw new RuntimeException("工作簿为空");
				}
				sheet = wbook.getSheet(sheetName);
				if (sheet == null) {
					msgBean.setIgnoreErr(true);
					throw new RuntimeException("工作簿中不存在页签:" + sheetName);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读取工作簿失败-" + xlsPath);
		}

	}

	/**
	 * 解析工作簿数据 包括合法性校验
	 * 
	 * @return
	 */
	protected boolean checkParsed() {
		commCheck();

		// 除去表头 所以从第一行遍历
		boolean checkResult = true;
		for (rowInx = 1; checkResult; rowInx++) {
			Row row = sheet.getRow(rowInx);
			if (row == null)
				break;

			// 单据编码
			String billNumber = getCellValue(row, 0);
			String orgNumber = getCellValue(row, 1);

			// 当单据编号为空 和公司编号为空时 ,结束遍历
			if (StringUtils.isEmpty(billNumber) && StringUtils.isEmpty(orgNumber))
				break;

			try {
				BillVO record = parseRow(row);
				// 新增或归集合并
				addOrUpdateRecords(record);
			} catch (ParseExcelException e) {
				// 解析一行记录，捕捉行记录的检查错误
				checkResult = false;
				msgBean.getErrList().add(e.getMessage());
				// 可以根据需要，决定是否继续校验，如果不需要校验，就是用 break返回即可
				// break;
			}

		}

		return checkResult;
	}

	protected String getCellValue(Row row, int cellIndex) {
		if (row.getCell(cellIndex) == null)
			return null;

		// 之所以要设置cellType ，是为了获得cell中原本的文本字段
		if (row.getCell(cellIndex).getCellType() != 1)
			row.getCell(cellIndex).setCellType(1);

		return row.getCell(cellIndex).toString();
	}

	protected Date getDateCellValue(Row row, int cellIndex) {
		if (row.getCell(cellIndex) == null || row.getCell(cellIndex).getCellType() != Cell.CELL_TYPE_NUMERIC)
			return null;

		return row.getCell(cellIndex).getDateCellValue();
	}

	/**
	 * 期望获得单元格内容为日期，但不确定是日期还是文本时，采用
	 * 
	 * @param cellIndex
	 * @return
	 */
	protected Date getDateValue(Row row, int cellIndex) {
		if (row.getCell(cellIndex) == null)
			return null;

		// 单元格 为 日期格式
		if (row.getCell(cellIndex).getCellType() == Cell.CELL_TYPE_NUMERIC)
			return getDateCellValue(row, cellIndex);

		// 单元格 为 文本格式
		if (row.getCell(cellIndex).getCellType() == Cell.CELL_TYPE_STRING) {
			String dateStr = row.getCell(cellIndex).getStringCellValue();
			Date date = conventToDate(dateStr, YYYY_MM_DD);
			if (date == null) {
				colInx = cellIndex;
				throw new RuntimeException("请确认字符串格式是否为yyyy-MM-dd");
			}
			return date;
		}

		return null;
	}

	/**
	 * 期望获得单元格内容为日期，但不确定是日期还是文本时，采用
	 * 
	 * @param cellIndex
	 * @return
	 */
	protected String getDateStrValue(Date date) {
		return DateToString2(date);
	}

	/**
	 * 期望获得单元格内容为日期，但不确定是日期还是文本时，采用
	 * 
	 * @param cellIndex
	 * @return
	 */
	protected String getTimeStrValue(Row row, int cellIndex) {
		if (row.getCell(cellIndex) == null)
			return null;
		String dateStr = "";

		// 单元格 为 日期格式
		if (row.getCell(cellIndex).getCellType() == Cell.CELL_TYPE_NUMERIC)
			dateStr = getDateCellValue(row, cellIndex).toString();

		// 单元格 为 文本格式
		if (row.getCell(cellIndex).getCellType() == Cell.CELL_TYPE_STRING) {
			dateStr = row.getCell(cellIndex).getStringCellValue();
			Date date = conventToDate(dateStr, YYYY_MM_DD);

			if (date == null) {
				colInx = cellIndex;
				throw new RuntimeException("请确认字符串格式是否为YYYY_MM_DD");
			}
			return DateToString(date);
		}
		return dateStr;
	}

	/**
	 * String转换成BigDecimal
	 * 
	 * @param obj
	 * @return
	 */
	public static BigDecimal convertToDecimal(String obj) {
		BigDecimal rtnValue = BigDecimal.ZERO;

		try {
			rtnValue = new BigDecimal(obj);
		} catch (Exception e) {
		}

		// 因为数量全部为非负数,需要返回成绝对值
		return rtnValue.abs();
	}

	/**
	 * 汇率字段,保留4位小数 String转换成BigDecimal
	 * 
	 * @param obj
	 * @return
	 */
	public static BigDecimal convertToRate(String obj) {
		BigDecimal rtnValue = BigDecimal.ZERO;

		try {
			rtnValue = new BigDecimal(obj).setScale(4, BigDecimal.ROUND_HALF_UP);
			System.out.println(rtnValue);
		} catch (Exception e) {
		}

		// 因为数量全部为非负数,需要返回成绝对值
		return rtnValue.abs();
	}

	/**
	 * Date类型 按照pattern格式 转换成字符串
	 * 
	 * @param date
	 * @param pattern
	 * @return
	 */
	public static String formatDate(Date date, String pattern) {
		String rtv = null;
		SimpleDateFormat sdf = new SimpleDateFormat(pattern);
		if (date != null) {
			rtv = sdf.format(date);
		}
		return rtv;
	}

	public static Date conventToDate(String dateStr, String format) {
		Date date = null;

		if (dateStr != null && dateStr.length() != 0) {
			SimpleDateFormat formattor = new SimpleDateFormat(format);
			try {
				date = formattor.parse(dateStr);
			} catch (ParseException e) {
				e.printStackTrace();
			}
		}

		return date;
	}

	/**
	 * 替换EL表达式
	 * 
	 * @param parseStr
	 * @param params
	 * @return
	 */
	public static String parseEL(String parseStr, String[] params) {
		Map<String, String> map = new HashMap<String, String>(params.length);
		try {
			for (int i = 0; i < params.length; i++) {
				map.put(i + "", params[i]);
			}
		} catch (Exception e) {
			throw new IllegalArgumentException("参数错误", e.getCause());
		}
		String regex = "\\$\\{([^\\}]+)\\}";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(parseStr);
		String g;
		while (m.find()) {
			g = m.group(1);
			parseStr = m.replaceFirst(map.get(g) + "");
			m = p.matcher(parseStr);
		}
		return parseStr;
	}

	public MessageBean getMsgBean() {
		return msgBean;
	}

	public static boolean isEmptyString(String str) {
		return str != null && str.trim().length() > 0 ? true : false;
	}

	public static boolean isEmptyObject(Object obj) {
		return obj == null ? true : false;
	}

	/**
	 * 将Date转换成String
	 * 
	 * @param date
	 * @return
	 */
	public String DateToString(Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String dateStr = sdf.format(date);
		return dateStr;
	}

	/**
	 * 将Date转换成String
	 * 
	 * @param date
	 * @return
	 */
	public String DateToString2(Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String dateStr = sdf.format(date);
		return dateStr;
	}
}
